<?php
/**
 * SQL.class.php - database operation wrapper class
 * 
 * HomepageCMS <http://homepage-cms.googlecode.com/>
 * Copyright (C) 2008 HomepageCMS
 * 
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program. If not, see <http://www.gnu.org/licenses/>.
 * 
 * @author Jesse Hannah <jesse@jbhannah.net>
 * @copyright Copyright (c) 2008 HomepageCMS
 * @package includes
 */

define('ORDER_ASCENDING', 1);
define('ORDER_DESCENDING', 2);

define('MYSQL_QUERY_SELECT', 1);
define('MYSQL_QUERY_INSERT', 2);
define('MYSQL_QUERY_UPDATE', 3);

/**
 * Database operation wrapper class
 * 
 * This class contains the functions used to connect to the MySQL database, and
 * add and retrieve information from it. The goal is to MINIMIZE the use of
 * database operation functions anywhere else in the code base, consolidating
 * as much SQL and as many database functions as possible into one place.
 * 
 * @package classes
 */
class SQL {
	/**
	 * Database reference object
	 * @var mysqli
	 * @access protected
	 */
	protected $db = null;
	
	/**
	 * Number of queries executed
	 * @var int
	 * @access protected
	 */
	protected $count = 0;
	
	/**
	 * Most recent result set returned by select()
	 * @access protected
	 */
	protected $result = null;
	
	/**
	 * Constructor function - instantiates the database connection
	 * 
	 * Creates a new mysqli instance and connects to the database. You must be
	 * using MySQL 4.1.3 or later (the current 4.1 version is 4.1.22, but the
	 * 4.1 series has been superseded by 5.0).
	 * 
	 * @return true|false False if any connection errors occur, otherwise true.
	 * @param array $dsn Array containing the hostname and port of the MySQL
	 * server, the username and password to be used to connect, and the name of
	 * the database.
	 */
	function __construct($dsn) {
		$this->db = new mysqli($dsn['hostname'], $dsn['username'], $dsn['password'],
			$dsn['database'], $dsn['port']);
		if (mysqli_connect_errno()) {
			$this->log_error("Database connection failed: " . mysqli_connect_error());
			return false;
		}
		return true;
	}
	
	/**
	 * Wrapper function for the query-executing classes. The first argument
	 * determines what type of query is to be executed; all other arguments are
	 * passed as the arguments for the select, update, or insert function. Then
	 * the query counter is incremented.
	 * 
	 * @return true|false False if any errors ocurred in query execution;
	 * otherwise true.
	 * @param $type Type of query to be executed (MYSQL_QUERY_SELECT,
	 * MYSQL_QUERY_UPDATE, or MYSQL_QUERY_INSERT).
	 */
	function query($type) {
		// Always do result_clear() after a select query when you're done with the result set!
		// Stop now before an error is thrown.
		if ($this->result) {
			return false;
		}
		
		// Get all of the arguments passed and assign them to variables for later use.
		// HACK UGLY DO NOT WANT. There should be a better way to do this...
		$n = 0;
		// Set $arg0 through $arg7 as null so that it doesn't ERRORZOMG on us if
		// we try to use one of them later and it isn't set. Warned you this was
		// ugly.
		for ($i = 0; $i <=7 ; $i++) {
			${"arg" . $i} = null;
		}
		foreach (func_get_args() as $arg) {
			${"arg" . $n++} = $arg;
		}
		// End of HACK UGLY DO NOT WANT
		
		$this->count++;
		
		switch ($type) {
			case MYSQL_QUERY_INSERT:
				return $this->insert($arg1, $arg2);
			case MYSQL_QUERY_UPDATE:
				return $this->update($arg1, $arg2, $arg3);
			case MYSQL_QUERY_SELECT:
			default:
				return $this->select($arg1, $arg2, $arg3, $arg4, $arg5, $arg6, $arg7);
		}
	}
	
	/**
	 * Gets information from the database. $select and $from are the only
	 * required parameters; all other values can be set, or omitted or set as
	 * null to ignore.
	 * 
	 * @param $select Comma-separated list of values to be selected, or COUNT(*)
	 * @param $from Name of table to retrieve data from
	 * @param $where Parameters for choosing rows to return values from ("id=#")
	 * @param $order Whether to sort the data ascending or descending
	 * @param $sort If given, is the name of the column to sort rows by
	 * @param $limit Maximum number of rows to fetch
	 * @param $offset Number of values by which to offset the returned results
	 * (for example, if "5" begins returning from the 6th result)
	 * @return true|false False if any errors ocurred in query execution;
	 * otherwise true.
	 * @access protected
	 */
	protected function select($select, $from, $where, $order, $sort, $limit, $offset) {
		$s = "SELECT (" . $select . ")";
		$f = " FROM (" . $from . ")";
		
		if ($where) {
			$w = " WHERE (" . $where . ")";
		}
		
		if ($order) {
			switch ($order) {
				case ORDER_DESCENDING:
					$o = " ORDER DESC";
					break;
				case ORDER_ASCENDING:
				default:
					$o = " ORDER ASC";
					break;
			}
			
			// Sort column requires order
			if ($sort) {
				$b = " BY (" . $sort . ")";
			}
		}
		
		if ($limit) {
			$l = " LIMIT (" . $limit . ")";
		}
		
		if ($offset) {
			$t = " OFFSET (" . $offset . ")";
		}
		
		// Concatenate all the parts of the query together
		$q = $s . $f . $w . $o . $b . $l . $t . "";
		
		if ($this->result = $this->db->query($q, MYSQL_USE_RESULT)) {
			return true;
		}
		
		$this->log_error("Query error: " . $this->db->error);
		return false;
	}
	
	/**
	 * Updates an entry in the database.
	 * 
	 * @param $update Field and value to be updated in the database ("field =
	 * value")
	 * @param $in Name of the table to be updated
	 * @param $where Parameter for selecting the row to be updated, preferrably
	 * the primary key (such as "id = #")
	 * @return true|false False if any errors ocurred in query execution;
	 * otherwise true.
	 * @access protected
	 */
	protected function update($update, $in, $where) {
		$u = "UPDATE (" . $update . ")";
		$i = " IN (" . $in . ")";
		$w = " WHERE (" . $where . ")";
		
		// Concatenate all the parts of the query together
		$q = $u . $i . $w . "";
		
		if ($this->db->query($q)) {
			return true;
		}
		
		$this->log_error("Query error: " . $this->db->error);
		return false;
	}
	
	/**
	 * Inserts a new row into a table in the database.
	 * 
	 * @param $insert Comma-separated list of values to be inserted into the
	 * database
	 * @param $into Name of the table to insert the values into
	 * @return true|false False if any errors ocurred in query execution;
	 * otherwise true.
	 * @access protected
	 */
	protected function insert($insert, $into) {
		$n = "INSERT (" . $insert . ")";
		$t = " INTO (" . $into . ")";
		
		// Concatenate all the parts of the query together
		$q = $n . $t . "";
		
		if ($this->db->query($q)) {
			return true;
		}
		
		$this->log_error("Query error: " . $this->db->error);
		return false;
	}
	
	/**
	 * Clear the MySQL result set for the next query.
	 */
	function result_clear() {
		$this->result->close();
		$this->result = null;
	}
	
	/**
	 * Logs any SQL errors encountered, and notifies the site administrator by
	 * email.
	 * 
	 * @param string $error Error message to be recorded
	 */
	function log_error(string $error) {
		$to = ADMIN_EMAIL;
		$subject = "SQL error ocurred with " . SITE_NAME;
		$body = "A MySQL error has ocurred with your HomepageCMS site " .
			SITE_NAME . ". The following error message was returned:"
			. LINE_ENDING . LINE_ENDING . "\"$error\"" . LINE_ENDING . LINE_ENDING
			. "Please attend to this problem immediately so as to minimize
			downtime and avoid further errors.";
		
		$errlog = fopen(CMS_DIR . "/error.log", "ab");
		if (!fwrite($errlog, $error . "" . LINE_ENDING)) {
			$body += "" . LINE_ENDING . LINE_ENDING .
				"In addition, the error message could not be written to the log
				file. Make sure the file " . CMS_DIR . "/error.log is writable.";
		}
		
		if (MAIL_SQL_ERRORS) {
			mail($to, $subject, wordwrap($body, "70", LINE_ENDING, true));
		}
	}
	
	/**
	 * Returns the number of queries executed.
	 * 
	 * @return Number of times query() has been called
	 */
	function query_count() {
		return $this->count;
	}
	
	/**
	 * Destructor function - close the database connection
	 * 
	 * Clears any query results still stored to free resources and closes the
	 * database connection when the SQL object is dereferenced.
	 */
	function __destruct() {
		if ($this->result) {
			$this->result_clear();
		}
		$this->db->close();
	}
}
?>
